package com.wrx.club_management_backend.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wrx.club_management_backend.entity.Club;
import org.apache.ibatis.annotations.*;
import org.springframework.web.bind.annotation.PathVariable;

import java.util.List;
import java.util.Map;

@Mapper
public interface ClubMapper extends BaseMapper<Club> {
    /**
     * 查询俱乐部列表并统计成员数量，只返回状态为1的社团
     *
     * @param keyWord 搜索关键词
     * @return 俱乐部列表
     */
    @Select({
            "<script>",
            "SELECT c.*, COUNT(DISTINCT CASE WHEN ucl.status = 1 THEN ucl.user_id ELSE NULL END) as memberCount",
            "FROM tb_club c",
            "LEFT JOIN user_club_link ucl ON c.id = ucl.club_id",
            "<where>",
            "    c.stat = 1", // 只查询状态为1的社团
            "    <if test='keyWord != null and keyWord != \"\"'>",
            "        AND (c.title LIKE CONCAT('%', #{keyWord}, '%')",
            "        OR c.description LIKE CONCAT('%', #{keyWord}, '%'))",
            "    </if>",
            "</where>",
            "GROUP BY c.id, c.title, c.description, c.avatar, c.establish_date,",
            "         c.activity_level, c.gender_ratio, c.favorite_count,",
            "         c.tags, c.create_time, c.update_time, c.stat", // 添加stat到GROUP BY子句
            "</script>"
    })
    @Results({
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "title", property = "title"),
            @Result(column = "description", property = "description"),
            @Result(column = "avatar", property = "avatar"),
            @Result(column = "establish_date", property = "establishDate"),
            @Result(column = "activity_level", property = "activityLevel"),
            @Result(column = "gender_ratio", property = "genderRatio"),
            @Result(column = "favorite_count", property = "favoriteCount"),
            @Result(column = "memberCount", property = "memberCount"),
            @Result(column = "tags", property = "tags"),
            @Result(column = "stat", property = "stat"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime")
    })
    List<Club> selectClubsWithMemberCount(@Param("keyWord") String keyWord);

    // 获取社团详情（包括成员数量）
    @Select("SELECT c.*, COUNT(ucl.user_id) AS members " +
            "FROM tb_club c " +
            "LEFT JOIN user_club_link ucl ON c.id = ucl.club_id " +
            "WHERE c.id = #{clubId} " +
            "GROUP BY c.id")
    Club selectClubDetail(@Param("clubId") Long clubId);

    // 获取社团活动列表
    @Select("SELECT * FROM tb_club_activity " +
            "WHERE club_id = #{clubId} AND status = 1 " +
            "ORDER BY begin_data DESC")
    List<Map<String, Object>> selectClubActivities(@Param("clubId") Long clubId);

    // 修改 selectClubMembers 方法，使用正确的表名
    @Select("SELECT u.*, cm.position, cm.department " +
            "FROM user_club_link ucl " +
            "LEFT JOIN tb_user u ON ucl.user_id = u.id " +  // 修改这里，从 sys_user 改为 tb_user
            "LEFT JOIN tb_club_manager cm ON u.id = cm.user_id AND cm.club_id = ucl.club_id " +
            "WHERE ucl.club_id = #{clubId} AND ucl.status = 1 " +  // 添加状态检查
            "ORDER BY CASE WHEN cm.position = '部长' THEN 0 " +     // 部长排在最前面
            "            WHEN cm.position = '副部长' THEN 1 " +
            "            ELSE 2 END")
    List<Map<String, Object>> selectClubMembers(@Param("clubId") Long clubId);

    // 修改查询部长信息的方法
    @Select("SELECT u.*" +
            "FROM tb_club_manager cm " +
            "JOIN tb_user u ON cm.user_id = u.id " +  // 这里也要修改表名
            "WHERE cm.club_id = #{clubId} " +
            "LIMIT 1")
    Map<String, Object> selectClubPresident(@Param("clubId") Long clubId);

    // 获取社团荣誉列表
    @Select("SELECT * FROM tb_club_achievement " +
            "WHERE club_id = #{clubId} " +
            "ORDER BY date DESC")
    List<Map<String, Object>> selectClubAchievements(@Param("clubId") Long clubId);

    // 检查用户是否已收藏社团
    @Select("SELECT COUNT(*) FROM tb_club_favorite " +
            "WHERE club_id = #{clubId} AND user_id = #{userId}")
    int checkFavorite(@Param("clubId") Long clubId, @Param("userId") Long userId);

    // 检查用户是否已报名活动
    @Select("SELECT COUNT(*) FROM tb_activity_signup " +
            "WHERE activity_id = #{activityId} AND user_id = #{userId} AND status = 1")
    int checkActivitySignup(@Param("activityId") Long activityId, @Param("userId") Long userId);

    // 添加收藏记录
    @Insert("INSERT INTO tb_club_favorite (club_id, user_id) VALUES (#{clubId}, #{userId})")
    int insertFavorite(@Param("clubId") Long clubId, @Param("userId") Long userId);

    // 删除收藏记录
    @Delete("DELETE FROM tb_club_favorite WHERE club_id = #{clubId} AND user_id = #{userId}")
    int deleteFavorite(@Param("clubId") Long clubId, @Param("userId") Long userId);

    // 添加活动报名记录
    @Insert("INSERT INTO tb_activity_signup (activity_id, user_id, status) VALUES (#{activityId}, #{userId}, 1)")
    int insertActivitySignup(@Param("activityId") Long activityId, @Param("userId") Long userId);


    // 检查用户是否已是社团成员
    @Select("SELECT COUNT(*) FROM user_club_link WHERE club_id = #{clubId} AND user_id = #{userId} AND status = 1")
    int checkClubMember(@Param("clubId") Long clubId, @Param("userId") Long userId);

    // 添加社团成员
    @Insert("INSERT INTO user_club_link(user_id, club_id) VALUES(#{userId}, #{clubId})")
    int insertClubMember(@Param("clubId") Long clubId, @Param("userId") Long userId);

    // 更新社团成员状态
    @Update("UPDATE user_club_link SET status = #{status} WHERE club_id = #{clubId} AND user_id = #{userId}")
    int updateClubMemberStatus(@Param("clubId") Long clubId, @Param("userId") Long userId, @Param("status") Integer status);

    // 添加管理层记录
    @Insert("INSERT INTO tb_club_manager(club_id, user_id, position, department) " +
            "VALUES(#{clubId}, #{userId}, #{position}, #{department})")
    int insertManager(Map<String, Object> params);

    // 更新管理层状态
    @Update("UPDATE tb_club_manager SET status = 0 " +
            "WHERE club_id = #{clubId} AND user_id = #{userId} AND position = #{position}")
    int updateManagerStatus(Map<String, Object> params);


    @Select("SELECT c.*, cm.position " +
            "FROM tb_club c " +
            "JOIN tb_club_manager cm ON c.id = cm.club_id " +
            "WHERE cm.user_id = #{userId} " +
            "AND c.stat = 1")
    @Results({
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "title", property = "title"),
            @Result(column = "des", property = "des"),
            @Result(column = "description", property = "description"),
            @Result(column = "establish_date", property = "establishDate"),
            @Result(column = "avatar", property = "avatar"),
            @Result(column = "member_count", property = "memberCount"),
            @Result(column = "activity_level", property = "activityLevel"),
            @Result(column = "favorite_count", property = "favoriteCount"),
            @Result(column = "position", property = "position")
    })
    List<Map<String, Object>> selectManagedClubs(@Param("userId") Long userId);

    /**
     * 查询用户参加的所有社团
     *
     * @param userId 用户ID
     * @return 社团列表
     */
    @Select("SELECT" +
            " * " +
            " FROM" +
            " tb_club c" +
            " INNER JOIN user_club_link tcm ON c.id = tcm.club_id AND  c.stat = 1" +
            " AND tcm.user_id = #{userId}")
    @Results({
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "title", property = "title"),
            @Result(column = "description", property = "description"),
            @Result(column = "avatar", property = "avatar"),
            @Result(column = "establish_date", property = "establishDate"),
            @Result(column = "activity_level", property = "activityLevel"),
            @Result(column = "gender_ratio", property = "genderRatio"),
            @Result(column = "favorite_count", property = "favoriteCount"),
            @Result(column = "tags", property = "tags"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime")
    })
    List<Club> getClubByUserId(@Param("userId") Long userId);


    /**
     * 从 user_club_link表中，删除记录
     * @param userId 用户 id
     * @param clubId 社团 id
     */
    @Delete("delete from user_club_link where user_id = #{userId} and club_id = #{clubId}")
    void removeUser(@Param("userId") Long userId,@Param("clubId") Long clubId);


    @Insert("insert into tb_club_manager(club_id, user_id) values(#{clubId}, #{userId})")
    void createManager(@Param("userId") Long userId,@Param("clubId") Long clubId);

    @Insert("insert into user_club_link(user_id, club_id) values(#{userId},#{clubId})")
    void createUserClubLink(@Param("userId") Long userId,@Param("clubId") Long clubId);

    @Delete("delete from user_club_link where user_id = #{userId}")
    void deleteClubParticipate(@Param("userId") Long userId);

    @Delete("delete from tb_club_manager where user_id = #{userId}")
    void deleteClubManager(@Param("userId") Long userId);
}